/**
 * 转换foods.txt数据格式的脚本
 * 将原始的中文字段名转换为系统需要的英文字段名
 */

import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

// 字段映射关系
const fieldMapping = {
  '能量（千卡）': 'energy_kcal',
  '蛋白质(g)': 'protein_g',
  '脂肪(g)': 'fat_g',
  '碳水化物(g)': 'carb_g',
  '膳食纤维(g)': 'fiber_g',
  '胆固醇(mg)': 'cholesterol_mg',
  '灰分(g)': 'ash_g',
  '维生素A(μgRE)': 'vitamin_a_ug',
  '硫胺素(mg)': 'thiamine_b1_mg',
  '核黄素(mg)': 'riboflavin_b2_mg',
  '维生素B6(mg)': 'vitamin_b6_mg',
  '维生素B12(mg)': 'vitamin_b12_mcg',
  '叶酸(ug)': 'folate_mcg',
  '烟碱(mg)': 'niacin_nicotinic_mg',
  '维生素C(mg)': 'vitamin_c_mg',
  '维生素E(mg)': 'vitamin_e_mg',
  '钙(mg)': 'calcium_mg',
  '磷(mg)': 'phosphorus_mg',
  '钾(mg)': 'potassium_mg',
  '钠(mg)': 'sodium_mg',
  '镁(mg)': 'magnesium_mg',
  '铁(mg)': 'iron_mg',
  '锌(mg)': 'zinc_mg',
  '硒(mg)': 'selenium_mcg',
  '铜(mg)': 'copper_mg',
  '锰(mg)': 'manganese_mg',
  '碘(mg)': 'iodine_mg',
  '胡萝卜素(μg)': 'carotene_ug',
  '视黄醇(μg)': 'retinol_ug',
  '尼克酸/烟酸(mg)': 'niacin_nicotinic_mg',
  'α-维生素E(mg)': 'alpha_vitamin_e_mg',
  '嘌呤': 'purine_mg'
};

// 英文名称映射（简化版，实际应该有完整的翻译）
const englishNames = {
  '膳力达水解乳清蛋白复合营养粉': 'Whey Protein Complex Nutrition Powder',
  '有机核桃黑芝麻粉': 'Organic Walnut Black Sesame Powder',
  '猴头菇石斛山药粉': 'Hericium Dendrobium Yam Powder',
  '黄豆腐': 'Yellow Tofu',
  '黑豆腐': 'Black Tofu',
  '鸡蛋': 'Egg',
  '鹰嘴豆': 'Chickpea',
  '雅培安素倍佳': 'Abbott Ensure Plus',
  '牛肉': 'Beef',
  '银鳕鱼': 'Silver Cod',
  '海参': 'Sea Cucumber',
  '三文鱼': 'Salmon',
  '黄鱼': 'Yellow Croaker',
  '鸡肉': 'Chicken',
  '虾': 'Shrimp',
  '花胶': 'Fish Maw',
  '鲳鱼': 'Pomfret',
  '小米': 'Millet',
  '粳米': 'Japonica Rice',
  '藜麦米': 'Quinoa',
  '燕麦片': 'Oats',
  '板栗': 'Chestnut',
  '红薯': 'Sweet Potato',
  '西红柿': 'Tomato',
  '紫甘蓝': 'Purple Cabbage',
  '绿甘蓝': 'Green Cabbage',
  '木耳': 'Black Fungus',
  '南瓜': 'Pumpkin',
  '杏鲍菇': 'King Oyster Mushroom',
  '秋葵': 'Okra',
  '豌豆苗': 'Pea Shoots',
  '银耳': 'White Fungus',
  '胡萝卜': 'Carrot',
  '白萝卜': 'White Radish',
  '羽衣甘蓝': 'Kale',
  '茄子': 'Eggplant',
  '青椒': 'Green Pepper',
  '黄辣椒': 'Yellow Pepper',
  '红辣椒': 'Red Pepper',
  '西兰花': 'Broccoli',
  '韭菜': 'Chinese Chives',
  '菠菜': 'Spinach',
  '黄瓜': 'Cucumber',
  '百合': 'Lily Bulb',
  '芹菜': 'Celery',
  '绿豆芽': 'Mung Bean Sprouts',
  '青菜白菜': 'Bok Choy',
  '红洋葱': 'Red Onion',
  '黄洋葱': 'Yellow Onion',
  '姜': 'Ginger',
  '芦笋': 'Asparagus',
  '芋头': 'Taro',
  '蕃茄': 'Tomato',
  '生菜': 'Lettuce',
  '甘蓝汁': 'Cabbage Juice',
  '芹菜汁': 'Celery Juice',
  '紫甘蓝汁': 'Purple Cabbage Juice',
  '包菜汁': 'Cabbage Juice',
  '羽衣甘蓝汁': 'Kale Juice',
  '梨': 'Pear',
  '苹果': 'Apple',
  '牛油果': 'Avocado',
  '西梅': 'Prune',
  '猕猴桃': 'Kiwi',
  '蓝莓': 'Blueberry',
  '橄榄油': 'Olive Oil',
  '亚麻籽油': 'Flaxseed Oil',
  '芝麻粉': 'Sesame Powder',
  '核桃': 'Walnut',
  '南瓜子': 'Pumpkin Seeds',
  '松子': 'Pine Nuts',
  '巴西莓粉': 'Acai Berry Powder'
};

// 富含标准（简化版）
const richStandards = {
  energy_kcal: 300, // 能量 > 300kcal/100g
  protein_g: 15,    // 蛋白质 > 15g/100g
  fat_g: 15,        // 脂肪 > 15g/100g
  carb_g: 60,       // 碳水化合物 > 60g/100g
  fiber_g: 5,       // 膳食纤维 > 5g/100g
  cholesterol_mg: 200, // 胆固醇 > 200mg/100g
  vitamin_a_ug: 600,   // 维生素A > 600μg/100g
  vitamin_c_mg: 30,    // 维生素C > 30mg/100g
  vitamin_e_mg: 10,    // 维生素E > 10mg/100g
  calcium_mg: 200,     // 钙 > 200mg/100g
  iron_mg: 5,          // 铁 > 5mg/100g
  zinc_mg: 3,          // 锌 > 3mg/100g
  potassium_mg: 500,   // 钾 > 500mg/100g
  magnesium_mg: 100,   // 镁 > 100mg/100g
  phosphorus_mg: 200,  // 磷 > 200mg/100g
  selenium_mcg: 15,    // 硒 > 15μg/100g
  thiamine_b1_mg: 0.5, // 硫胺素 > 0.5mg/100g
  riboflavin_b2_mg: 0.5, // 核黄素 > 0.5mg/100g
  niacin_nicotinic_mg: 5, // 烟酸 > 5mg/100g
  copper_mg: 1,        // 铜 > 1mg/100g
  manganese_mg: 2,     // 锰 > 2mg/100g
  carotene_ug: 1000,   // 胡萝卜素 > 1000μg/100g
  purine_mg: 150       // 嘌呤 > 150mg/100g（高嘌呤）
};

function convertFood(originalFood, index) {
  // 处理已经转换过的数据和原始数据
  const foodName = originalFood['食材名称'] || originalFood.name_zh;

  const converted = {
    id: index + 1,
    name_zh: foodName,
    name_en: englishNames[foodName] || foodName,
    is_system: true
  };

  // 如果是已经转换过的数据，直接返回
  if (originalFood.name_zh && originalFood.name_en) {
    return originalFood;
  }

  // 转换营养素字段
  for (const [chineseField, englishField] of Object.entries(fieldMapping)) {
    const value = originalFood[chineseField];

    if (value !== null && value !== undefined) {
      // 特殊处理能量字段（原数据看起来已经是每100g的数据）
      if (englishField === 'energy_kcal') {
        converted[englishField] = value;
      } else if (englishField === 'selenium_mcg') {
        // 硒的单位转换：mg -> mcg
        converted[englishField] = value * 1000;
      } else {
        converted[englishField] = value;
      }

      // 添加富含标志
      const richField = englishField.replace(/_(kcal|g|mg|mcg|ug)$/, '_rich');
      const standard = richStandards[englishField];

      if (standard !== undefined) {
        // 对于胆固醇和嘌呤，高含量不标记为"富含"
        if (englishField === 'cholesterol_mg' || englishField === 'purine_mg') {
          converted[richField] = false;
        } else {
          converted[richField] = value >= standard;
        }
      } else {
        converted[richField] = false;
      }
    } else {
      // 如果值为null，设置默认值
      const richField = englishField.replace(/_(kcal|g|mg|mcg|ug)$/, '_rich');
      converted[richField] = false;
    }
  }

  return converted;
}

async function convertFoodsData() {
  try {
    // 读取原始数据
    const inputPath = path.join(__dirname, '../database/foods.txt');
    const outputPath = path.join(__dirname, '../database/foods_converted.json');
    
    const rawData = fs.readFileSync(inputPath, 'utf8');
    const originalFoods = JSON.parse(rawData);
    
    // 转换数据
    const convertedFoods = originalFoods.map(convertFood);
    
    // 写入转换后的数据
    fs.writeFileSync(outputPath, JSON.stringify(convertedFoods, null, 2), 'utf8');
    
    console.log(`Successfully converted ${convertedFoods.length} foods`);
    console.log(`Output saved to: ${outputPath}`);
    
    // 生成SQL插入语句
    generateSQLInserts(convertedFoods);
    
  } catch (error) {
    console.error('Error converting foods data:', error);
  }
}

function generateSQLInserts(foods) {
  const sqlPath = path.join(__dirname, '../database/foods_converted.sql');
  let sql = '-- 转换后的食物数据插入语句\n\n';
  
  sql += 'INSERT INTO foods (\n';
  sql += '  name_zh, name_en, is_system,\n';
  sql += '  energy_kcal, energy_rich, protein_g, protein_rich, fat_g, fat_rich,\n';
  sql += '  carb_g, carb_rich, fiber_g, fiber_rich, cholesterol_mg, cholesterol_rich,\n';
  sql += '  vitamin_a_ug, vitamin_a_rich, vitamin_c_mg, vitamin_c_rich, vitamin_e_mg, vitamin_e_rich,\n';
  sql += '  calcium_mg, calcium_rich, phosphorus_mg, phosphorus_rich, potassium_mg, potassium_rich,\n';
  sql += '  sodium_mg, sodium_rich, magnesium_mg, magnesium_rich, iron_mg, iron_rich,\n';
  sql += '  zinc_mg, zinc_rich, selenium_mcg, selenium_rich\n';
  sql += ') VALUES\n';
  
  const values = foods.map(food => {
    return `  ('${food.name_zh}', '${food.name_en}', ${food.is_system ? 'TRUE' : 'FALSE'},\n` +
           `   ${food.energy_kcal || 'NULL'}, ${food.energy_rich}, ${food.protein_g || 'NULL'}, ${food.protein_rich}, ${food.fat_g || 'NULL'}, ${food.fat_rich},\n` +
           `   ${food.carb_g || 'NULL'}, ${food.carb_rich}, ${food.fiber_g || 'NULL'}, ${food.fiber_rich}, ${food.cholesterol_mg || 'NULL'}, ${food.cholesterol_rich},\n` +
           `   ${food.vitamin_a_ug || 'NULL'}, ${food.vitamin_a_rich}, ${food.vitamin_c_mg || 'NULL'}, ${food.vitamin_c_rich}, ${food.vitamin_e_mg || 'NULL'}, ${food.vitamin_e_rich},\n` +
           `   ${food.calcium_mg || 'NULL'}, ${food.calcium_rich}, ${food.phosphorus_mg || 'NULL'}, ${food.phosphorus_rich}, ${food.potassium_mg || 'NULL'}, ${food.potassium_rich},\n` +
           `   ${food.sodium_mg || 'NULL'}, ${food.sodium_rich}, ${food.magnesium_mg || 'NULL'}, ${food.magnesium_rich}, ${food.iron_mg || 'NULL'}, ${food.iron_rich},\n` +
           `   ${food.zinc_mg || 'NULL'}, ${food.zinc_rich}, ${food.selenium_mcg || 'NULL'}, ${food.selenium_rich})`;
  });
  
  sql += values.join(',\n') + ';\n';
  
  fs.writeFileSync(sqlPath, sql, 'utf8');
  console.log(`SQL insert statements saved to: ${sqlPath}`);
}

// 运行转换
if (import.meta.url === `file://${process.argv[1]}`) {
  convertFoodsData();
}

export { convertFoodsData, convertFood };
